Module doanhso
    '2008-09-22
    'Thuan Nguyen
    Public Sub doanhso_thuong_trinhduocvien(ByVal tuNgay As Date, ByVal denNgay As Date)
        Dim sqlQuery As String = ""

        doanhso_thuong(tuNgay, denNgay)

        sqlQuery = "DELETE FROM tbl_dot_temp_doanhso_thuong_trinhduocvien"
        moduleDatabaseConn.ExecuteNonQuery(sqlQuery)

        sqlQuery = "INSERT INTO tbl_dot_temp_doanhso_thuong_trinhduocvien "
        sqlQuery += "SELECT ma_khachhang, count(ma_phu), SUM(tien_mk), SUM(tien_mk_vang), SUM(tien_mk_heso)"
        sqlQuery += ", SUM(tien_mkp), SUM(tien_mkp_vang), SUM(tien_mkp_heso)"
        sqlQuery += ", SUM(tien_doanhso), ketluan, SUM(tien_thuong_khachhang)"
        sqlQuery += " FROM tbl_dot_temp_doanhso_thuong"
        sqlQuery += " GROUP BY ma_khachhang"
        moduleDatabaseConn.ExecuteNonQuery(sqlQuery)
    End Sub

    '2008-09-22
    'Thuan Nguyen
    'Add
    Public Sub doanhso_thuong(ByVal tuNgay As Date, ByVal denNgay As Date)
        Dim sqlQuery As String = ""
        Dim data_temp As DataTable

        sqlQuery = "DELETE FROM tbl_dot_temp_doanhso_thuong"
        moduleDatabaseConn.ExecuteNonQuery(sqlQuery)

        'Sam pham MEKOPHAR
        sqlQuery = "INSERT INTO tbl_dot_temp_doanhso_thuong "
        sqlQuery += "(ma_khachhang, ma_phu, tien_mk, tien_mk_vang, tien_mk_heso, tien_mkp, tien_mkp_vang, tien_mkp_heso, tien_doanhso, ketluan, tien_thuong_khachhang)"
        sqlQuery += "SELECT t1.ma_khachhang, t1.ma_phu, 0, 0, 0, SUM(t2.thanhtien), 0, 0, 0, '', 0 "
        sqlQuery += "FROM tbl_xuat_hanghoa as t1, tbl_xuat_chitiet_hanghoa as t2 "
        sqlQuery += "WHERE t1.ma_chungtu = t2.ma_chungtu AND t2.loaixuat = 0 "
        sqlQuery += "AND t2.ma_hanghoa NOT IN (SELECT ma_hanghoa FROM tbl_dot_temp_sanphammekong) "
        sqlQuery += "AND t1.ngay_chungtu BETWEEN '" & Format(tuNgay, "yyyy-MM-dd") & "' "
        sqlQuery += "AND '" & Format(denNgay, "yyyy-MM-dd") & "' "
        sqlQuery += "GROUP BY t1.ma_khachhang, t1.ma_phu "
        moduleDatabaseConn.ExecuteNonQuery(sqlQuery)

        'Sam pham MEKONG
        sqlQuery = "SELECT t1.ma_khachhang, t1.ma_phu, SUM(t2.thanhtien) "
        sqlQuery += "FROM tbl_xuat_hanghoa as t1, tbl_xuat_chitiet_hanghoa as t2 "
        sqlQuery += "WHERE t1.ma_chungtu = t2.ma_chungtu AND t2.loaixuat = 0 "
        sqlQuery += "AND t2.ma_hanghoa IN (SELECT ma_hanghoa FROM tbl_dot_temp_sanphammekong) "
        sqlQuery += "AND t1.ngay_chungtu BETWEEN '" & Format(tuNgay, "yyyy-MM-dd") & "' "
        sqlQuery += "AND '" & Format(denNgay, "yyyy-MM-dd") & "' "
        sqlQuery += "GROUP BY t1.ma_khachhang, t1.ma_phu "
        data_temp = moduleDatabaseConn.GetDataTable(sqlQuery)
        Dim ma_khachhang, ma_phu As String
        Dim tien_mk As Double
        Dim existed As Integer
        For i As Integer = 0 To data_temp.Rows.Count - 1
            ma_khachhang = data_temp.Rows.Item(i).Item(0)
            ma_phu = data_temp.Rows.Item(i).Item(1)
            tien_mk = data_temp.Rows.Item(i).Item(2)
            sqlQuery = "SELECT * FROM tbl_dot_temp_doanhso_thuong"
            sqlQuery += " WHERE ma_khachhang = '" & ma_khachhang & "'"
            sqlQuery += " AND ma_phu = '" & ma_phu & "'"
            existed = moduleDatabaseConn.CheckRecordExisted(sqlQuery)
            If existed = 1 Then ' Cap nhat tien Mekong
                sqlQuery = "UPDATE tbl_dot_temp_doanhso_thuong"
                sqlQuery += " SET tien_mk = " & CDec(tien_mk)
                sqlQuery += " WHERE ma_khachhang = '" & ma_khachhang & "'"
                sqlQuery += " AND ma_phu = '" & ma_phu & "'"
                moduleDatabaseConn.ExecuteNonQuery(sqlQuery)
            ElseIf existed = 0 Then ' Insert tien MEkong
                sqlQuery = "INSERT INTO tbl_dot_temp_doanhso_thuong"
                sqlQuery += " (ma_khachhang, ma_phu, tien_mk, tien_mk_vang, tien_mk_heso, tien_mkp, tien_mkp_vang, tien_mkp_heso, tien_doanhso, ketluan, tien_thuong_khachhang)"
                sqlQuery += " VALUES("
                sqlQuery += " '" & ma_khachhang & "'"
                sqlQuery += ", '" & ma_phu & "'"
                sqlQuery += ", " & CDec(tien_mk)
                sqlQuery += ", 0, 0, 0, 0, 0, 0, '', 0"
                sqlQuery += ")"
                moduleDatabaseConn.ExecuteNonQuery(sqlQuery)
            End If
        Next

        'Update nha thuoc thuong
        sqlQuery = "UPDATE tbl_dot_temp_doanhso_thuong"
        sqlQuery += " SET tien_mk_vang = tien_mk"
        sqlQuery += ", tien_mk_heso = tien_mk_vang * " & globalVariale.HESO_MEKONG & ""
        sqlQuery += ", tien_mkp_vang = tien_mkp"
        sqlQuery += ", tien_mkp_heso = tien_mkp_vang * " & globalVariale.HESO_MEKOPHAR & ""
        sqlQuery += ", tien_doanhso = tien_mk_heso + tien_mkp_heso"
        sqlQuery += " WHERE ma_phu NOT IN (SELECT ma_khachhang FROM tbl_dot_temp_khachhang_vang)"
        moduleDatabaseConn.ExecuteNonQuery(sqlQuery)

        'Update nha thuoc vang, bac, dong
        sqlQuery = "UPDATE tbl_dot_temp_doanhso_thuong"
        sqlQuery += " SET tien_mk_vang = tien_mk * " & CDec(globalVariale.PHANTRAM_MEKONG) & " / " & 100
        sqlQuery += ", tien_mk_heso = tien_mk_vang * " & globalVariale.HESO_MEKONG & ""
        sqlQuery += ", tien_mkp_vang = tien_mkp * " & CDec(globalVariale.PHANTRAM_MEKOPHAR) & " / " & 100
        sqlQuery += ", tien_mkp_heso = tien_mkp_vang * " & globalVariale.HESO_MEKOPHAR & ""
        sqlQuery += ", tien_doanhso = tien_mk_heso + tien_mkp_heso"
        sqlQuery += " WHERE ma_phu IN (SELECT ma_khachhang FROM tbl_dot_temp_khachhang_vang)"
        moduleDatabaseConn.ExecuteNonQuery(sqlQuery)
    End Sub

    '2008-09-23
    'ThuanNguyen
    Public Sub doanhso_thuong_chitiet(ByVal tuNgay As Date, ByVal denNgay As Date, ByVal doituong As String, ByVal listHangHoa As ArrayList)
        Dim sqlQuery As String = ""
        Dim data_temp As DataTable

        sqlQuery = "DELETE FROM tbl_dot_temp_doanhso_thuong"
        moduleDatabaseConn.ExecuteNonQuery(sqlQuery)

        'Sam pham MEKOPHAR
        sqlQuery = "INSERT INTO tbl_dot_temp_doanhso_thuong "
        sqlQuery += "(ma_khachhang, ma_phu, tien_mk, tien_mk_vang, tien_mk_heso, tien_mkp, tien_mkp_vang, tien_mkp_heso, tien_doanhso, ketluan, tien_thuong_khachhang)"
        sqlQuery += "SELECT t1.ma_khachhang, t1.ma_phu, 0, 0, 0, SUM(t2.thanhtien), 0, 0, 0, '', 0 "
        sqlQuery += "FROM tbl_xuat_hanghoa as t1, tbl_xuat_chitiet_hanghoa as t2 "
        sqlQuery += "WHERE t1.ma_chungtu = t2.ma_chungtu AND t2.loaixuat = 0 "
        sqlQuery += "AND t2.ma_hanghoa NOT IN (SELECT ma_hanghoa FROM tbl_dot_temp_sanphammekong) "
        sqlQuery += "AND t1.ngay_chungtu BETWEEN '" & Format(tuNgay, "yyyy-MM-dd") & "' "
        sqlQuery += "AND '" & Format(denNgay, "yyyy-MM-dd") & "' "
        sqlQuery += "GROUP BY t1.ma_khachhang, t1.ma_phu "
        moduleDatabaseConn.ExecuteNonQuery(sqlQuery)

        'Sam pham MEKONG
        sqlQuery = "SELECT t1.ma_khachhang, t1.ma_phu, SUM(t2.thanhtien) "
        sqlQuery += "FROM tbl_xuat_hanghoa as t1, tbl_xuat_chitiet_hanghoa as t2 "
        sqlQuery += "WHERE t1.ma_chungtu = t2.ma_chungtu AND t2.loaixuat = 0 "
        sqlQuery += "AND t2.ma_hanghoa IN (SELECT ma_hanghoa FROM tbl_dot_temp_sanphammekong) "
        sqlQuery += "AND t1.ngay_chungtu BETWEEN '" & Format(tuNgay, "yyyy-MM-dd") & "' "
        sqlQuery += "AND '" & Format(denNgay, "yyyy-MM-dd") & "' "
        sqlQuery += "GROUP BY t1.ma_khachhang, t1.ma_phu "
        data_temp = moduleDatabaseConn.GetDataTable(sqlQuery)
        Dim ma_khachhang, ma_phu As String
        Dim tien_mk As Double
        Dim existed As Integer
        For i As Integer = 0 To data_temp.Rows.Count - 1
            ma_khachhang = data_temp.Rows.Item(i).Item(0)
            ma_phu = data_temp.Rows.Item(i).Item(1)
            tien_mk = data_temp.Rows.Item(i).Item(2)
            sqlQuery = "SELECT * FROM tbl_dot_temp_doanhso_thuong"
            sqlQuery += " WHERE ma_khachhang = '" & ma_khachhang & "'"
            sqlQuery += " AND ma_phu = '" & ma_phu & "'"
            existed = moduleDatabaseConn.CheckRecordExisted(sqlQuery)
            If existed = 1 Then ' Cap nhat tien Mekong
                sqlQuery = "UPDATE tbl_dot_temp_doanhso_thuong"
                sqlQuery += " SET tien_mk = " & CDec(tien_mk)
                sqlQuery += " WHERE ma_khachhang = '" & ma_khachhang & "'"
                sqlQuery += " AND ma_phu = '" & ma_phu & "'"
                moduleDatabaseConn.ExecuteNonQuery(sqlQuery)
            ElseIf existed = 0 Then ' Insert tien MEkong
                sqlQuery = "INSERT INTO tbl_dot_temp_doanhso_thuong"
                sqlQuery += " (ma_khachhang, ma_phu, tien_mk, tien_mk_vang, tien_mk_heso, tien_mkp, tien_mkp_vang, tien_mkp_heso, tien_doanhso, ketluan, tien_thuong_khachhang)"
                sqlQuery += " VALUES("
                sqlQuery += " '" & ma_khachhang & "'"
                sqlQuery += ", '" & ma_phu & "'"
                sqlQuery += ", " & CDec(tien_mk)
                sqlQuery += ", 0, 0, 0, 0, 0, 0, '', 0"
                sqlQuery += ")"
                moduleDatabaseConn.ExecuteNonQuery(sqlQuery)
            End If
        Next

        'Update nha thuoc thuong
        sqlQuery = "UPDATE tbl_dot_temp_doanhso_thuong"
        sqlQuery += " SET tien_mk_vang = tien_mk"
        sqlQuery += ", tien_mk_heso = tien_mk_vang * " & globalVariale.HESO_MEKONG & ""
        sqlQuery += ", tien_mkp_vang = tien_mkp"
        sqlQuery += ", tien_mkp_heso = tien_mkp_vang * " & globalVariale.HESO_MEKOPHAR & ""
        sqlQuery += ", tien_doanhso = tien_mk_heso + tien_mkp_heso"
        sqlQuery += " WHERE ma_phu NOT IN (SELECT ma_khachhang FROM tbl_dot_temp_khachhang_vang)"
        moduleDatabaseConn.ExecuteNonQuery(sqlQuery)

        'Update nha thuoc vang, bac, dong
        sqlQuery = "UPDATE tbl_dot_temp_doanhso_thuong"
        sqlQuery += " SET tien_mk_vang = tien_mk * " & CDec(globalVariale.PHANTRAM_MEKONG) & " / " & 100
        sqlQuery += ", tien_mk_heso = tien_mk_vang * " & globalVariale.HESO_MEKONG & ""
        sqlQuery += ", tien_mkp_vang = tien_mkp * " & CDec(globalVariale.PHANTRAM_MEKOPHAR) & " / " & 100
        sqlQuery += ", tien_mkp_heso = tien_mkp_vang * " & globalVariale.HESO_MEKOPHAR & ""
        sqlQuery += ", tien_doanhso = tien_mk_heso + tien_mkp_heso"
        sqlQuery += " WHERE ma_phu IN (SELECT ma_khachhang FROM tbl_dot_temp_khachhang_vang)"
        moduleDatabaseConn.ExecuteNonQuery(sqlQuery)
    End Sub
End Module
